﻿Imports System.Data.SqlServerCe
Imports Framework.Data

Namespace Data
    Public Class SaveCommand(Of PrimaryKeyType)
        Private mTableName As String
        Private mParams As New Generic.List(Of Param)
        Private mIdentityColName As String
        Private mIdentityValue As PrimaryKeyType
        Private mIdentityNullValue As PrimaryKeyType
        Private mIsInsertEx As Boolean = False
        Private mIsIdentity As Boolean = False

        Public Sub New(ByVal tableName As String, ByVal identityColName As String, ByVal identityValue As PrimaryKeyType, ByVal identityNullValue As PrimaryKeyType)
            mTableName = tableName
            mIdentityNullValue = identityNullValue
            mIdentityColName = String.Format("[{0}]", identityColName)
            mIdentityValue = identityValue
            mIsInsertEx = mIdentityValue.Equals(mIdentityNullValue)
            mIsIdentity = True
        End Sub

        Public Sub New(ByVal tableName As String, ByVal primaryKeyColName As String, ByVal primaryKeyValue As PrimaryKeyType)
            mTableName = tableName
            mIdentityColName = String.Format("[{0}]", primaryKeyColName)
            mIdentityValue = primaryKeyValue
        End Sub

        Public ReadOnly Property IsInsert() As Boolean
            Get
                Return mIsInsertEx
            End Get
        End Property

        Public Sub AddParam(ByVal name As String, ByVal value As Object, ByVal nullable As Boolean)
            Dim clsParam As New Param
            clsParam.ColName = String.Format("[{0}]", name)
            clsParam.VariableName = String.Format("@{0}", name)

            If value Is Nothing And Not nullable Then
                Throw New ArgumentNullException("Value cannot be null if column is not nullable!")
            ElseIf value Is Nothing Then
                clsParam.Value = DBNull.Value
            ElseIf TypeOf value Is Integer AndAlso CInt(value) = 0 And nullable Then
                clsParam.Value = DBNull.Value
            ElseIf TypeOf value Is Short AndAlso CShort(value) = 0 And nullable Then
                clsParam.Value = DBNull.Value
            ElseIf TypeOf value Is Byte AndAlso CByte(value) = 0 And nullable Then
                clsParam.Value = DBNull.Value
            ElseIf TypeOf value Is Date AndAlso CDate(value) = DateTime.MinValue Then
                clsParam.Value = DBNull.Value
            Else
                clsParam.Value = value
            End If
            mParams.Add(clsParam)
        End Sub

        Public Function Save() As PrimaryKeyType
            Dim oRet As PrimaryKeyType
            Using t As New Framework.Data.TransactionScope(TransactionScope.TransactionMode.NullTransaction)
                oRet = Save(t)
                t.Complete()
            End Using
            Return oRet
        End Function

        Public Class NoSuchRowException
            Inherits Exception
            Public Sub New(ByVal message As String)
                MyBase.New(message)
            End Sub
        End Class
        Public Class RowNotUniqueException
            Inherits Exception
            Public Sub New(ByVal message As String)
                MyBase.New(message)
            End Sub
        End Class


        Public Function Save(ByVal transactionScope As TransactionScope) As PrimaryKeyType
            If mIsIdentity Then
                Dim sqlCmd As Command = GenerateSqlCommand(mIsIdentity, mIsInsertEx, transactionScope)

                Dim iRowsAffected As Integer = sqlCmd.ExecuteNonQuery

                Select Case iRowsAffected
                    Case Is < 1
                        Throw New NoSuchRowException("No row was affected by id-based update!")
                    Case Is > 1
                        Throw New RowNotUniqueException(String.Format("Expected just one row to be affected but in fact {0} were affected!!", iRowsAffected))
                End Select

                If mIsInsertEx Then
                    Dim sqlCmd2 As New Command("SELECT @@Identity", transactionScope.Connection)
                    sqlCmd2.Transaction = transactionScope
                    Using dr As SqlCeDataReader = sqlCmd2.ExecuteReader
                        If Not dr.Read Then Throw New ApplicationException("Could not get the identity of newly inserted row!")
                        mIdentityValue = CType(dr.GetValue(0), PrimaryKeyType)
                    End Using
                End If
                Return mIdentityValue
            Else
                Dim sqlCmd As Command = GenerateSqlCommand(mIsIdentity, False, transactionScope)

                Dim iRowsAffected As Integer = sqlCmd.ExecuteNonQuery

                Select Case iRowsAffected
                    Case 1
                        Return mIdentityValue
                    Case Is > 1
                        Throw New ApplicationException(String.Format("Expected just one row to be affected but in fact {0} were affected!!", iRowsAffected))
                    Case 0
                        'now, we need to do an insert
                End Select

                sqlCmd = GenerateSqlCommand(mIsIdentity, True, transactionScope)

                iRowsAffected = sqlCmd.ExecuteNonQuery

                Return mIdentityValue
            End If
        End Function

        Private Function GenerateSqlCommand(ByVal isIdentity As Boolean, ByVal generateInsert As Boolean, ByVal transactionScope As TransactionScope) As Command
            Dim clsSB As New System.Text.StringBuilder
            If generateInsert Then
                clsSB.Append("INSERT INTO ")
            Else
                clsSB.Append("UPDATE ")
            End If
            clsSB.Append(mTableName)
            If generateInsert Then
                clsSB.Append(" (")
                For i As Integer = 0 To mParams.Count - 1
                    If i > 0 Then clsSB.Append(","c)
                    With mParams(i)
                        clsSB.Append(.ColName)
                    End With
                Next
                If Not isIdentity Then
                    clsSB.Append(","c)
                    clsSB.Append(mIdentityColName)
                End If
                clsSB.Append(") VALUES (")
                For i As Integer = 0 To mParams.Count - 1
                    If i > 0 Then clsSB.Append(","c)
                    With mParams(i)
                        clsSB.Append(.VariableName)
                    End With
                Next
                If Not isIdentity Then
                    clsSB.Append(",@PrimKey")
                End If
                clsSB.Append(")"c)
            Else
                clsSB.Append(" SET ")
                For i As Integer = 0 To mParams.Count - 1
                    If i > 0 Then clsSB.Append(", ")
                    With mParams(i)
                        clsSB.Append(.ColName)
                        clsSB.Append("="c)
                        clsSB.Append(.VariableName)
                    End With
                Next
                clsSB.Append(" WHERE ")
                clsSB.Append(mIdentityColName)
                clsSB.Append("=@Identity")
                'clsSB.Append(mIdentityValue)
            End If

            Dim sqlCmd As Command = Environment.NewCommand(clsSB.ToString, transactionScope)
            For Each clsParam As Param In mParams
                If TypeOf (clsParam.Value) Is Byte() Then
                    sqlCmd.AddParameter(clsParam.VariableName, SqlDbType.Image).Value = clsParam.Value
                Else
                    sqlCmd.AddParameterWithValue(clsParam.VariableName, clsParam.Value)
                End If
            Next
            If generateInsert And Not isIdentity Then
                sqlCmd.AddParameterWithValue("@PrimKey", mIdentityValue)
            ElseIf Not generateInsert Then
                sqlCmd.AddParameterWithValue("@Identity", mIdentityValue)
            End If

            Return sqlCmd
        End Function

        Private Class Param
            Public ColName As String
            Public VariableName As String
            Public Value As Object
        End Class
    End Class
End Namespace